import pymysql


class as400_sync():
    def __init__(self, insert_dataDict):
        self.db_user = 'ly'
        self.db_pwd = '123'
        self.db_name = 'ocr'
        self.db_host = '10.10.1.1'
        self.db_table = 'as400_sync'

        self.conn = self.create_conn()
        self.cursor = self.conn.cursor()   # 光标

        self.insert_Group_dict(insert_dataDict)

        # self.check_db()


    def create_conn(self):
        try:
            # cursorclass=cursors.DictCursor  字典数据返回
            conn = pymysql.connect(host=self.db_host, port=3306, user=self.db_user, password=self.db_pwd,
                           database=self.db_name, charset='utf8', cursorclass=pymysql.cursors.DictCursor)
            print("%s 用户连接 %s 数据库 %s 成功! " % (self.db_user, self.db_host, self.db_name))
            return conn
        except:
            return None

    def check_db(self):
        get_user_sql = ' select user, host from mysql.user '
        self.cursor.execute(get_user_sql)
        get_user = self.cursor.fetchall()
        print("数据已有用户及访问方式: \n", get_user)
        get_db_sql = ' show databases '
        self.cursor.execute(get_db_sql)
        db_list = self.cursor.fetchall()
        print("该服务器包含的数据库有: \n", db_list)
        get_db_table_sql = ' show tables '
        self.cursor.execute(get_db_table_sql)
        db_table = self.cursor.fetchall()
        print("%s 数据库所有表格如下: \n%s" % (self.db_name, db_table))

    # 字典方式插入数据
    def insert_Group_dict(self, insert_dataDict=None):
        """ 字典方式插入数据 多组数据插入  防SQL注入插入数据
            insert_dataDict: 插入的数据, 单条数据为字典, 多条插入为列表套字典,
            serial_number: 唯一码,
            db_name: 数据表名
        """
        de_weight = True
        # 检查数据是否有重复的
        # try:
        #     de_weight = self.item_getdata(db_name=db_name, serial_number=serial_number)
        # except:
        #     de_weight = ()
        if de_weight:   # 如有重复数据 停止插入
            # 这里db_name和insert_dataDict，其中insert_dataDict是一个字典，写插入数据库的代码
            # 获取插入数据的头部数据 KEYS  列名
            # 传进来参数为字典, 当insert_dataDict为字典时 为单个数据插入
            if isinstance(insert_dataDict, dict):
                cols = ", ".join('`{}`'.format(k) for k in insert_dataDict.keys())
                # 获取值
                val_cols = ', '.join('%({})s'.format(k) for k in insert_dataDict.keys())
                a = [insert_dataDict]  # 拼出要插入的内容
            # 数据为列表嵌套字典时为多字典/多条数据插入
            elif isinstance(insert_dataDict, list):
                cols = ", ".join('`{}`'.format(k) for k in insert_dataDict[0].keys())
                # 获取值
                val_cols = ', '.join('%({})s'.format(k) for k in insert_dataDict[0].keys())
                a = insert_dataDict

            # 获取插入SQL 命令 insert_dataDict
            sql = "insert into {}(%s) values(%s)".format(self.db_table)
            res_sql = sql % (cols, val_cols)
            try:
                self.cursor.executemany(res_sql, a)  # 将字典列表a传入
                self.cursor.connection.commit()
                # self.conn.commit()
            except Exception as e:
                print("字典数据插入异常: ", e)
                self.conn.rollback()   # 回滚 插入数据无效
        else:
            print("数据重复插入")






if __name__=='__main__':
    a400_sync()





